Sql 2005 Backup Prosedure - Shrink Db

05.05.2011
DECLARE @DataBase_Name NVARCHAR(50);
DECLARE @Backup_String NVARCHAR(4000);
SET @DataBase_Name = 'DBNAME';

SELECT @Backup_String = 'DUMP TRANSACTION ['+@DataBase_Name+'] WITH NO_LOG DBCC SHRINKDATABASE (['+@DataBase_Name+'], TRUNCATEONLY) ' +
'BACKUP DATABASE ['+@DataBase_Name+'] TO DISK = N''E:\Sql_Server_Backup\Sql2005\'+@DataBase_Name+'\'+@DataBase_Name+'_'+ 
CONVERT(NVARCHAR, YEAR(GETDATE())) + '-' + 
CASE WHEN MONTH(GETDATE()) < 10 THEN '0'+CONVERT(NVARCHAR, MONTH(GETDATE())) ELSE CONVERT(NVARCHAR, MONTH(GETDATE())) END  + '-' + 
CASE WHEN DAY(GETDATE()) < 10 THEN '0'+CONVERT(NVARCHAR, DAY(GETDATE())) ELSE CONVERT(NVARCHAR, DAY(GETDATE())) END + '-' + 
SUBSTRING(CONVERT(NVARCHAR,GETDATE(),8),1,2) + '-' + 
SUBSTRING(CONVERT(NVARCHAR,GETDATE(),8),4,2) + '-' + 
SUBSTRING(CONVERT(NVARCHAR,GETDATE(),8),7,2) +'.bak'' WITH INIT, NOUNLOAD, NAME = N'''+@DataBase_Name+''', NOSKIP , STATS = 10, NOFORMAT'

EXEC (@Backup_String)

0 kişi tarafından 0 olarak değerlendirildi

  • Currently 5.0/5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5

Sql Server 2005, , ,